﻿using System;
using System.Data;
using System.Data.OleDb;

namespace UnitTest
{
    public static class Common
    {
        public static DataTable GetExcelData( string sheetName)
        {
            const string fileName = "DataTest.xls";
            var sql = "";
            var conn = new OleDbConnection();
            var adapter = new OleDbDataAdapter();
            var dt = new DataTable();
            var command = conn.CreateCommand();
            const string excelFile = fileName;
            var excelType = System.IO.Path.GetExtension(excelFile);
            var connstr = "";

            if (excelType == "XLSX")
                connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0";
            else
                connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0; OLE DB Services=-4";

            conn.ConnectionString = connstr;
            sql = "SELECT * FROM [" + sheetName + "$]";

            command.CommandText = sql;
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            return dt;
        }
    }
}